﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Koubei.DataAccess.SqlClient;

namespace DegreeDistance
{
    class Program
    {
        static void Main(string[] args)
        {
            //for (int i = 0; i < 20; i++)
            //{
            //    int a = 0 + i;
            //    int d = ConvertPrecision5(a);
            //    Console.Write(a);
            //    Console.Write("\t");
            //    Console.Write(Math.Round((a/5.0d)));
            //    Console.Write("\t");
            //    Console.WriteLine(d);
            //    Console.WriteLine();
            //}
            //return;

            var all = GetAll();
            foreach (DataRow dr in all.Rows)
            {
                var lng = Convert.ToDouble(dr["longitude"]);
                var lat = Convert.ToDouble(dr["latitude"]);
                var id = Convert.ToInt32(dr["Id"]);
                var code = Convert.ToString(dr["code"]);
                var area  = Convert.ToString(dr["area"]);

                var p = Mercator.ToPixel(lng, lat);
                var pid = GetPixelId(p);

                Console.ForegroundColor = ConsoleColor.Yellow;
                Console.WriteLine("[lng={0}\tlat={1}]\t", lng, lat);

                Console.ForegroundColor = ConsoleColor.Green;
                Console.WriteLine("x={0}\ty={1}", Convert.ToInt32(Math.Round(p.X, 0))/5,
                                  Convert.ToInt32(Math.Round(p.Y, 0))/5);
                Console.ForegroundColor = ConsoleColor.Magenta;
                Console.WriteLine("x={0}\ty={1}", ConvertPrecision5(Convert.ToInt32(Math.Round(p.X, 0))),
                                  ConvertPrecision5(Convert.ToInt32(Math.Round(p.Y, 0))));

                Console.WriteLine();

                var px = Convert.ToInt32(Math.Round(p.X/5.0d, 0));
                var py = Convert.ToInt32(Math.Round(p.Y/5.0d, 0));
                UpdateWeizhangArea(px, py, code, area);

                //Console.WriteLine("PId={0}\r\n", pid);
                //UpdateP(id, p);
            }
            Console.ResetColor();
        }

        static int ConvertPrecision5(int a)
        {
            bool isMinus = false;
            if( a<0 )
            {
                a = Math.Abs(a);
                isMinus = true;
            }

            int b = (a / 10) * 10;
            int c = a - b;
            if (c <= 3) c = 0;
            if (c > 3 && c <= 7) c = 5;
            if (c > 7) c = 10;
            int d = b + c;

            return isMinus ? 0 - d : d;
        }

        static void UpdateWeizhangArea(int px,int py,string code,string area)
        {
            var q = new SqlQuery();
            q.CommandText =
@"MERGE weizhangarea AS t
USING (SELECT @px,@py,@code,@area) AS s (px,py,code,area)
ON (t.px=s.px AND t.py=s.py AND t.code=s.code AND t.area=s.area)
WHEN matched THEN
	UPDATE SET num=num+1
WHEN NOT matched THEN
	INSERT (px,py,code,area,num)
	VALUES(@px,@py,@code,@area,1);";
            q.Parameters.Add("@px", px, SqlDbType.Int);
            q.Parameters.Add("@py", py, SqlDbType.Int);
            q.Parameters.Add("@code", code, SqlDbType.VarChar, 16);
            q.Parameters.Add("@area", area, SqlDbType.VarChar, 100);
            SqlDataAccess.ExecScalar(q);
        }

        static string GetPixelId(Pixel p)
        {
            // 精确到5米
            int x = Convert.ToInt32(Math.Round(p.X, 0))/5;
            int y = Convert.ToInt32(Math.Round(p.Y, 0))/5;

            return string.Concat(x.ToString("X8"), "X", y.ToString("X8"));
        }

        static DataTable GetAll()
        {
            var q = new SqlQuery();
            q.CommandText = "SELECT Id,ViolationInfoId,longitude,latitude,code,area FROM recode WITH(NOLOCK) where Id>0";
            return SqlDataAccess.ExecDataset(q).Tables[0];
        }

        static void UpdateP(int id,Pixel p)
        {
            var q = new SqlQuery();
            q.CommandText = "UPDATE recode SET PX=@x, PY=@y, PId=@pid WHERE Id=@id";
            q.Parameters.Add("@x", p.X, SqlDbType.Decimal);
            q.Parameters.Add("@y", p.Y, SqlDbType.Decimal);
            q.Parameters.Add("@pid", GetPixelId(p), SqlDbType.Char, 17);
            q.Parameters.Add("@id", id, SqlDbType.Int);
            SqlDataAccess.ExecScalar(q);
        }
    }
}
